﻿using System;
using System.Data.SqlClient;
using LoverMeeting.DataBase.DataMappers;
using LoverMeeting.Model.Entity;
using LoverMeeting.Model.Repository;

namespace LoverMeeting.DataBase
{
    public class LoverRepository : DaoRepositoryBase<Lover>, ILoverRepository
    {
        public LoverRepository() : base(new LoverMapper())
        {
        }

        public void Create(Lover lover)
        {
            if (lover.Account != null)
                SaveLoverAccount(lover.Account);

            if (lover.Configuration != null)
                SaveConfiguration(lover.Configuration);
            
            var command = GetSqlCommand(
                @"INSERT INTO Lover 
                  (Nickname, Birthday, Email, Password, GenderId, CityId, LoverAccountId, LoverConfigurationId) 
                  VALUES 
                  (@nickname, @birthday, @email, CONVERT(NVARCHAR(32), HashBytes('MD5', @password), 2), @genderid, @cityid, @loveraccountid, @loverconfigurationid)");

            command.Parameters.AddWithValue("Nickname", lover.Nickname);
            command.Parameters.AddWithValue("Birthday", lover.Birthday);
            command.Parameters.AddWithValue("Email", lover.Email);
            command.Parameters.AddWithValue("Password", lover.Password);
            if(lover.Gender != null && lover.Gender.Id != 0)
                command.Parameters.AddWithValue("GenderId", lover.Gender.Id);
            else
                command.Parameters.AddWithValue("GenderId", DBNull.Value);
            if(lover.City != null && lover.City.Id != 0)
                command.Parameters.AddWithValue("CityId", lover.City.Id);
            else
                command.Parameters.AddWithValue("CityId", DBNull.Value);
            if(lover.Account != null && lover.Account.Id != 0)
                command.Parameters.AddWithValue("LoverAccountId", lover.Account.Id);
            else
                command.Parameters.AddWithValue("LoverAccountId", DBNull.Value);
            if(lover.Configuration != null && lover.Configuration.Id != 0)
                command.Parameters.AddWithValue("LoverConfigurationId", lover.Configuration.Id);
            else
                command.Parameters.AddWithValue("LoverConfigurationId", DBNull.Value);
            
            Save(command);
            lover.Id = GetScopeId();

            if(lover.Avatar != null && !string.IsNullOrEmpty(lover.Avatar.Url))
                SaveImage(lover);

            CloseConnection();
        }

        public void Update(Lover lover)
        {
            if(lover.Account != null)
                SaveLoverAccount(lover.Account);
            
            if(lover.Configuration != null)
                SaveConfiguration(lover.Configuration);
        
            var command = GetSqlCommand(
                @"UPDATE Lover SET
                  Birthday = @birthday, GenderId = @genderid, LoverAccountId = @loveraccountid, 
                  LoverConfigurationId = @loverconfigurationid
                  WHERE Id = @LoverId");

            command.Parameters.AddWithValue("Birthday", lover.Birthday);
            command.Parameters.AddWithValue("GenderId", lover.Gender.Id);
            command.Parameters.AddWithValue("LoverAccountId", lover.Account.Id);
            command.Parameters.AddWithValue("LoverConfigurationId", lover.Configuration.Id);
            command.Parameters.AddWithValue("LoverId", lover.Id);
            
            Save(command);

            if (lover.Avatar != null && !string.IsNullOrEmpty(lover.Avatar.Url))
                SaveImage(lover);
            
            CloseConnection();
        }

        private void SaveConfiguration(LoverConfiguration configuration)
        {
            SqlCommand command = null;
            if (configuration.Id == 0)
                command = GetSqlCommand(
                    @"INSERT INTO LoverConfiguration
                          (DesiredGenderId, MaritalStatusId, LoverHeightId, LoverWeightId, RelationshipTypeId, Status, Description)
                          VALUES
                          (@DesiredGenderId, @MaritalStatusId, @LoverHeightId, @LoverWeightId, @RelationshipType, @Status, @Description)");
            else
                command = GetSqlCommand(
                    @"UPDATE LoverConfiguration SET
                          DesiredGenderId = @DesiredGenderId, MaritalStatusId = @MaritalStatusId, 
                          LoverHeightId = @LoverHeightId, LoverWeightId = @LoverWeightId, Status = @Status, 
                          Description = @Description");

            command.Parameters.AddWithValue("DesiredGenderId", configuration.DesiredGender.Id);
            command.Parameters.AddWithValue("MaritalStatusId", configuration.MaritalStatus.Id);
            command.Parameters.AddWithValue("LoverHeightId", configuration.Height.Id);
            command.Parameters.AddWithValue("LoverWeightId", configuration.Weight.Id);
            command.Parameters.AddWithValue("RelationshipType", configuration.RelationshipType.Id);
            command.Parameters.AddWithValue("Status", configuration.Status);
            if(!string.IsNullOrEmpty(configuration.Description))
                command.Parameters.AddWithValue("Description", configuration.Description);
            else
                command.Parameters.AddWithValue("Description", DBNull.Value);

            Save(command);
            if (configuration.Id == 0)
                configuration.Id = GetScopeId();
        }

        private void SaveLoverAccount(LoverAccount account)
        {
            SqlCommand command = null;
            if (account.Id == 0)
                command = GetSqlCommand(
                    @"INSERT INTO LoverAccount 
                        (IsActive, ActivationCode, IsBlockedByAdmin, IsAdmin)
                        VALUES
                        (@isactive, @activationcode, @isblockedbyadmin, @isadmin)");
            else
                command = GetSqlCommand(
                    @"Update LoverAccount SET
                        IsActive = @isactive, ActivationCode = @activationcode, 
                        IsBlockedByAdmin = @isblockedbyadmin, IsAdmin = @isadmin
                        WHERE Id = @loveraccountid");

            command.Parameters.AddWithValue("IsActive", account.IsActive);
            command.Parameters.AddWithValue("ActivationCode", account.ActivationCode);
            command.Parameters.AddWithValue("IsBlockedByAdmin", account.IsBlockedByAdmin);
            command.Parameters.AddWithValue("IsAdmin", account.IsAdmin);
            command.Parameters.AddWithValue("LoverAccountId", account.Id);
            Save(command);
            if(account.Id == 0)
                account.Id = GetScopeId();
        }

        private void SaveImage(Lover lover)
        {
            SqlCommand command = null;
            if (lover.Avatar.Id == 0)
                command = GetSqlCommand(
                    @"INSERT INTO Image
                          (Name, Url, LoverId)
                          VALUES
                          (@ImageName, @ImageUrl, @LoverId)");
            else
                command = GetSqlCommand(
                    @"UPDATE Image SET
                          Name = @ImageName, Url = @ImagemUrl, LoverId = @LoverId");
            
            command.Parameters.AddWithValue("ImageName", lover.Avatar.Name);
            command.Parameters.AddWithValue("ImageUrl", lover.Avatar.Url);
            command.Parameters.AddWithValue("LoverId", lover.Id);

            Save(command);
            if (lover.Avatar.Id == 0)
                lover.Avatar.Id = GetScopeId();
        }

        public void Delete(int id)
        {
            var lover = new Lover { Id = id };
            var command = GetSqlCommand(@"DELETE FROM Lover WHERE Id = @loverid");
            command.Parameters.AddWithValue("LoverId", id);
            Save(command);
            CloseConnection();
        }

        public void DeleteImage(int imageId)
        {
            var command = GetSqlCommand(@"DELETE FROM Image WHERE Id = @imageid");
            command.Parameters.AddWithValue("ImageId", imageId);
            Save(command);
            CloseConnection();
        }

        public Lover GetBy(int id)
        {
            var command = GetSqlCommand(
                @"SELECT l.Id as LoverId, Nickname, Birthday, Email, g.Id as GenderId, 
                  g.Description as GenderDescription, c.Id as CityId, c.Name as CityName,
                  p.Id as ProvinceId, p.Name as ProvinceName,
                  la.Id as LoverAccountId, la.IsActive, la.ActivationCode, la.IsBlockedByAdmin, la.IsAdmin,
                  lc.Id as LoverConfigurationId, lc.Status, lc.Description,
                  dg.Id as DesiredGenderId, dg.Description as DesiredGenderDescription,
                  ms.Id as MaritalStatusId, ms.Description as MaritalStatusDescription,
                  lh.Id as LoverHeightId, lh.Description as LoverHeightDescription,
                  lw.Id as LoverWeightId, lw.Description as LoverWeightDescription,  
                  rt.Id as RelationshipTypeId, rt.Description as RelationshipTypeDescription,
                  i.Id as ImageId, i.Url as ImageUrl, i.Name as ImageName
                  FROM Lover l 
                  left join Gender g on l.GenderId = g.Id 
                  left join City c on l.CityId = c.Id
                  left join Province p on c.ProvinceId = p.Id
                  left join LoverAccount la on l.LoverAccountId = la.Id
                  left join LoverConfiguration lc on l.LoverConfigurationId = lc.Id
                  left join DesiredGender dg on lc.DesiredGenderId = dg.Id
                  left join MaritalStatus ms on lc.MaritalStatusId = ms.Id
                  left join LoverHeight lh on lc.LoverHeightId = lh.Id
                  left join LoverWeight lw on lc.LoverWeightId = lw.Id
                  left join RelationshipType rt on lc.RelationshipTypeId = rt.Id
                  left join Image i on l.Id = i.LoverId
                  WHERE l.Id = @loverid");
            command.Parameters.AddWithValue("LoverId", id);
            return GetSingle(command);
        }

        public Lover GetBy(string email, string password)
        {
            var command = GetSqlCommand(
                @"SELECT l.Id as LoverId, Nickname, Birthday, Email, g.Id as GenderId, 
                  g.Description as GenderDescription, c.Id as CityId, c.Name as CityName,
                  p.Id as ProvinceId, p.Name as ProvinceName,
                  la.Id as LoverAccountId, la.IsActive, la.ActivationCode, la.IsBlockedByAdmin, la.IsAdmin,
                  lc.Id as LoverConfigurationId, lc.Status, lc.Description,
                  dg.Id as DesiredGenderId, dg.Description as DesiredGenderDescription,
                  ms.Id as MaritalStatusId, ms.Description as MaritalStatusDescription,
                  lh.Id as LoverHeightId, lh.Description as LoverHeightDescription,
                  lw.Id as LoverWeightId, lw.Description as LoverWeightDescription,  
                  rt.Id as RelationshipTypeId, rt.Description as RelationshipTypeDescription,
                  i.Id as ImageId, i.Url as ImageUrl, i.Name as ImageName
                  FROM Lover l 
                  left join Gender g on l.GenderId = g.Id 
                  left join City c on l.CityId = c.Id
                  left join Province p on c.ProvinceId = p.Id
                  left join LoverAccount la on l.LoverAccountId = la.Id
                  left join LoverConfiguration lc on l.LoverConfigurationId = lc.Id
                  left join DesiredGender dg on lc.DesiredGenderId = dg.Id
                  left join MaritalStatus ms on lc.MaritalStatusId = ms.Id
                  left join LoverHeight lh on lc.LoverHeightId = lh.Id
                  left join LoverWeight lw on lc.LoverWeightId = lw.Id
                  left join RelationshipType rt on lc.RelationshipTypeId = rt.Id
                  left join Image i on l.Id = i.LoverId
                  WHERE l.Email = @email and l.Password = CONVERT(NVARCHAR(32), HashBytes('MD5', @password), 2)");
            
            command.Parameters.AddWithValue("@email", email);
            command.Parameters.AddWithValue("@password", password);
            return GetSingle(command);
        }
    }
}
